## Ranges of different spend-downs  
# reorder(organization_name, pull(summarize(spend_down, sd = sd(group_by(spend_down, pct_spend_down)))), na.rm = TRUE)
# Reordering by standard deviation of pct_spend down
spend_down_box <- spend_down %>%
  group_by(organization_name) %>%
  filter(pct_spend_down != Inf) %>%
  summarize(sd = sd(pct_spend_down, na.rm = TRUE)) %>%
  right_join(spend_down, by = "organization_name") %>%
  select(organization_name, EIN, pct_spend_down, sd) %>%
  mutate(organization_name = reorder(organization_name, -sd, na.rm = TRUE))

## Unlimited 
box_plot <- ggplot(spend_down_box, aes(x = organization_name, y = pct_spend_down)) + 
  geom_boxplot(aes(color = organization_name), show.legend = FALSE) + 
  geom_point(size = 1, alpha = 0.5) + 
  theme_bw() + 
  labs(title = "Range of Endowment Percent Change per Company",
       x = "Dance Company",
       y = "Percentage of Change in Endowment Balance") + 
  theme(axis.text.x = element_blank()) + 
  geom_hline(yintercept = 100, linetype = "dotted", color = "maroon")
ggplotly(box_plot) %>%
  layout(showlegend = FALSE)

##Limited to 100 for visibility 
box_plot_lim <- ggplot(spend_down_box, aes(x = organization_name, y = pct_spend_down)) + 
  geom_boxplot(aes(color = organization_name), show.legend = FALSE) + 
  geom_point(size = 1, alpha = 0.5) + 
  theme_bw() + 
  labs(title = "Range of Endowment % Change (Max of 100) per Company",
       x = "Dance Company",
       y = "Percentage of Change in Endowment Balance") + 
  theme(axis.text.x = element_blank()) + 
  scale_y_continuous(breaks = scales::breaks_pretty(n = 20),
                     limit = c(-100,100))
ggplotly(box_plot_lim)  %>%
  layout(showlegend = FALSE)

## Spend Down over Time
spend_down_plot <- spend_down %>%
  ggplot(aes(x = fiscal_year, y = pct_spend_down, 
             group = organization_name, color = organization_name)) +
  geom_line(alpha = 0.5) + 
  theme_bw() + 
  labs(y = "Percent Spend Down",
       x = "Fiscal Year",
       title = "Percentage of Change in Endowment Balance",
       subtitle = "By Fiscal Year") + 
  theme(plot.title = element_text(size = 10, face = "bold", hjust = .5),
        axis.title = element_text(size = 12, face = "bold"),
        plot.subtitle = element_text(size = 5, face = "italic", hjust = .5),
        axis.text.x = element_text(size = 10, angle = 25),
        strip.text = element_text(face="bold",size = 5),
        legend.key.size = unit(1, 'mm'),
        legend.text = element_text(size=7)) + 
  scale_y_continuous(labels = scales::comma_format(),
                     breaks = scales::pretty_breaks(n = 20)) +
  geom_hline(yintercept = 100, linetype = "dotted", color = "gray")
ggplotly(spend_down_plot) 

##Plot with Y scale between -100 and 100 
limited_scale <- spend_down %>%
  ggplot(aes(x = fiscal_year, y = pct_spend_down, 
             group = organization_name, color = organization_name)) +
  geom_line(show.legend = FALSE, alpha = 0.5) + 
  theme_bw() + 
  labs(y = "Percent Spend Down",
       x = "Fiscal Year",
       title = "Percentage of Change in Endowment Balance (max 100)",
       subtitle = "By Fiscal Year") + 
  theme(plot.title = element_text(size = 10, face = "bold", hjust = .5),
        axis.title = element_text(size = 12, face = "bold"),
        plot.subtitle = element_text(size = 5, face = "italic", hjust = .5),
        axis.text.x = element_text(size = 10, angle = 25),
        strip.text = element_text(face="bold",size = 5),
        legend.key.size = unit(1, 'mm'),
        legend.text = element_text(size=7)) + 
  scale_y_continuous(labels = scales::comma_format(),
                     breaks = scales::pretty_breaks(n = 20),
                     limits = c(-100, 100))
ggplotly(limited_scale)

## Pandemic Years
spend_down_plot <- spend_down %>%
  filter(fiscal_year %in% c("2018", "2019", "2020", "2021", "2022")) %>% 
  ggplot(aes(x = fiscal_year, y = pct_spend_down, 
             group = organization_name, color = organization_name)) +
  geom_line(show.legend = FALSE, alpha = 0.5) + 
  theme_bw() + 
  labs(y = "Percent Spend Down",
       x = "Fiscal Year",
       title = "Percentage of Change in Endowment Balance",
       subtitle = "Within Pandemic Years") + 
  theme(plot.title = element_text(size = 10, face = "bold", hjust = .5),
        axis.title = element_text(size = 12, face = "bold"),
        plot.subtitle = element_text(size = 5, face = "italic", hjust = .5),
        axis.text.x = element_text(size = 10, angle = 25),
        strip.text = element_text(face="bold",size = 5),
        legend.key.size = unit(1, 'mm'),
        legend.text = element_text(size=7)) + 
  scale_y_continuous(labels = scales::comma_format(),
                     breaks = scales::pretty_breaks(n = 20)) +
  geom_hline(yintercept = 100, linetype = "dotted", color = "gray")
ggplotly(spend_down_plot) 

## Table of available in-pandemic data 
spend_down %>%
  filter(fiscal_year %in% c("2019", "2020", "2021", "2022")) %>%
  select(organization_name, pct_spend_down, fiscal_year) %>%
  arrange(desc(fiscal_year)) %>%
  make_table(title = "Percentage of Change in Endowment Balance within Pandemic Years", col_names = c("Name", "% Spend Down", "Year")) %>%
  scroll_box(height = "450px")

spend_down %>%
  filter(fiscal_year %in% c("2019", "2020", "2021", "2022")) %>%
  select(organization_name, pct_spend_down, fiscal_year) %>%
  group_by(fiscal_year) %>%
  summarize(total_in_year = n())